package edu.cmu.cs.webapp.ioms.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import edu.cmu.cs.webapp.ioms.dao.ClientDAO;
import edu.cmu.cs.webapp.ioms.dao.DataAccessException;
import edu.cmu.cs.webapp.ioms.models.Client;
import edu.cmu.cs.webapp.ioms.models.IOMSPackage;
import edu.cmu.cs.webapp.ioms.models.OrderStatus;
import edu.cmu.cs.webapp.ioms.models.PurchaseOrder;

public class ClientDAOImpl extends DAO implements ClientDAO {

	public ClientDAOImpl() {
		super();
	}

	/**
	 * 
	 * @return
	 */
	public List<Client> getAllClient() throws DataAccessException{
		Connection con = null;
		Client c = null;
        List<Client> clst = new ArrayList<Client>();
        try {
        	con = getConnection();
        	Statement stmt = con.createStatement();
        	String sql = "select * from client";
        	ResultSet rs = stmt.executeQuery(sql);
        	while(rs.next()){
        		c = IOMSPackage.eINSTANCE.getIOMSFactory().createClient();
        		
        		c.setClient_id(rs.getInt("client_id"));
        		c.setLname(rs.getString("lname"));
        		c.setFname(rs.getString("fname"));
        		c.setEmail(rs.getString("email"));
        		c.setPhone(rs.getString("phone"));
        		c.setCompany(rs.getString("company"));
        		c.setAddress(rs.getString("address"));
        		c.setCity(rs.getString("city"));
        		c.setState(rs.getString("state"));
        		c.setCountry(rs.getString("country"));

        		clst.add(c);
        	}
            stmt.close();
            con.close();    	
        } catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DataAccessException(e);
        }
        return clst;
	}

	@Override
	public int addClient(Client client) throws DataAccessException {
		// TODO Auto-generated method stub
		Connection con = null;
		int result = -1;
		try {
			con = getConnection();
			
            PreparedStatement pstmt = con.prepareStatement(
            "insert into client(lname, fname, email, phone, company," +
            "address, city, state, country) " +
            "values (? ,?, ?, ?, ?, ?, ?, ?, ?)");
            
            pstmt.setString(1, client.getLname());
            pstmt.setString(2, client.getFname());
            pstmt.setString(3, client.getEmail());
            pstmt.setString(4, client.getPhone());
            pstmt.setString(5, client.getCompany());
            pstmt.setString(6, client.getAddress());
            pstmt.setString(7, client.getCity());
            pstmt.setString(8, client.getState());
            pstmt.setString(9, client.getCountry());
            
            pstmt.executeUpdate();
            System.out.println("insert: " + client.toString());
            
			Statement stmt = con.createStatement();
			String sql = "select * from client where client_id=(select max(client_id) from client)";
			ResultSet rs = stmt.executeQuery(sql);

			if (rs.next())
				result = rs.getInt("client_id");
			
			pstmt.close();
			stmt.close();
			con.close();
			
			log("Create new client [" + client.toString() + "]", "Add");
			
		} catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DataAccessException(e);
		}
		return result;
	}

	@Override
	public boolean deleteClient(Client client) throws DataAccessException {
		// TODO Auto-generated method stub
		Connection con = null;
		try {
			con = getConnection();		
            PreparedStatement pstmt = con.prepareStatement(
            "delete from client where client_id =" + client.getClient_id());
            pstmt.executeUpdate();
            System.out.println("delete: " + client.toString());
            		
			pstmt.close();
			con.close();
			
			log("Delete client [" + client.toString() + "]", "Delete");
			
		} catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
        	throw new DataAccessException(e);
		}
		return true;
	}

	@Override
	public boolean updateClient(Client client) throws DataAccessException {
		// TODO Auto-generated method stub
		Connection con = null;
		try {
			con = getConnection();
			
			String str="update client set ";
			if( client.getLname()!= null )
				str += "lname = '" + client.getLname() + "',";
			if( client.getFname() != null)
				str += "fname ='" + client.getFname() + "',";
			if( client.getEmail() != null)
				str += "email='" + client.getEmail() +"',";
			if( client.getPhone() != null )
				str += "phone='" + client.getPhone() +"',";
			if( client.getCompany() != null )
				str += "company='" + client.getCompany() +"',";	
			if( client.getAddress() != null )
				str += "address='" + client.getAddress() +"',";	
			if( client.getCity() != null )
				str += "city='" + client.getCity() +"',";
			if( client.getState() != null )
				str += "state='" + client.getState() +"',";
			if( client.getCountry() != null )
				str += "country='" + client.getCountry() +"',";
			
			str = str.substring(0, str.lastIndexOf(","));
			str += " where client_id=" + client.getClient_id();	
			System.out.println(str);
            PreparedStatement pstmt = con.prepareStatement(str);
            pstmt.executeUpdate();
            System.out.println("update: " + client.toString());
            		
			pstmt.close();
			con.close();
			
			log("Update client [" + client.toString() + "]", "Update");
			
		} catch (Exception e) {
            try { if (con != null) con.close(); } catch (SQLException e2) { /* ignore */ }
            throw new DataAccessException(e);
		}
		return true;
	}
}
